package excel.study.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.vo.BaseEntityTypeConstants;
import com.alibaba.fastjson.JSONObject;
import excel.study.dao.MyExcelExportService;
import excel.study.test.ExcelStyleUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * 基于注解导出excel
 */
@RestController
@RequestMapping("/annotation")
public class AnnotationController {

    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        //填充数据
        ExcelExportEntity idEntity = new ExcelExportEntity("ID", "id");
        ExcelExportEntity nameEntity = new ExcelExportEntity("姓名", "name");
        ExcelExportEntity ageEntity = new ExcelExportEntity("年龄", "age");

        idEntity.setType(BaseEntityTypeConstants.FUNCTION_TYPE);
        String[] replace = new String[3];
        replace[0] = "12";
        replace[1] = "123";
        replace[2] = "124";
        idEntity.setReplace(replace);
        idEntity.setDict("ID");

        List<Map<String, Object>> sheetsList = new ArrayList<>();
        List<ExcelExportEntity> columns = Arrays.asList(idEntity, nameEntity, ageEntity);
        ExportParams exportParams  = new ExportParams(null, "ceshi");
        exportParams.setType(ExcelType.HSSF);
        exportParams.setStyle(ExcelStyleUtil.class);

        List<JSONObject> jsonObject = new ArrayList<>();
//        for (int i = 0; i < 3; i++) {
//            JSONObject json = new JSONObject();
//            json.put("id",12);
//            json.put("name",456);
//            json.put("age",789);
//            jsonObject.add(json);
//        }

        //调用 方法exportParams为设置的sheet页信息 list为拼装好的导出字段信息  jsonObjects为数据项
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columns, jsonObject);
        Sheet sheet = workbook.getSheetAt(0);
        sheet.createFreezePane(0,0);
        FileOutputStream fileOutputStream = new FileOutputStream("D:\\home\\excel\\test.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        workbook.close();
    }


    @GetMapping("/exportExcel2")
    public void exportExcel2(HttpServletResponse response) throws IOException {
        //填充数据
        ExcelExportEntity idEntity = new ExcelExportEntity("ID", "id");
        ExcelExportEntity nameEntity = new ExcelExportEntity("姓名", "name");
        ExcelExportEntity ageEntity = new ExcelExportEntity("年龄", "age");

        List<JSONObject> jsonObject = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            JSONObject json = new JSONObject();
            json.put("id",1231);
            json.put("name",456);
            json.put("age",789);
            jsonObject.add(json);
        }

        //定义表格样式
        List<ExcelExportEntity> modelList =  Arrays.asList(idEntity, nameEntity, ageEntity);
        // Sheet1样式
        ExportParams sheet1ExportParams = new ExportParams();
        // 设置sheet得名称
        sheet1ExportParams.setSheetName("入库统计");
        // 创建sheet1使用得map
        Map<String, Object> sheet1ExportMap = new HashMap<>();
        // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
        sheet1ExportMap.put("title", sheet1ExportParams);
        //sheet1样式
        sheet1ExportMap.put("entityList", modelList);
        //sheet1中要填充得数据,true表示查询入库数据，false表示查询易签待入库数据
        sheet1ExportMap.put("data", jsonObject);
        //Sheet2设置
        ExportParams sheet2ExportParams = new ExportParams();
        sheet2ExportParams.setSheetName("易签待入库统计");
        Map<String, Object> sheet2ExportMap = new HashMap<>();
        sheet2ExportMap.put("title", sheet2ExportParams);
        sheet2ExportMap.put("entityList", modelList);
        sheet2ExportMap.put("data", jsonObject);
        // 将sheet1、sheet2使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(sheet1ExportMap);
        sheetsList.add(sheet2ExportMap);
        // 执行方法
        Workbook workBook = exportExcel(sheetsList);
        //设置response
        response.setHeader("content-disposition", "attachment;filename=" + "ceshi" + ".xls");
        //设置编码格式
        response.setCharacterEncoding("GBK");
        Sheet sheet = workBook.getSheetAt(0);
        sheet.createFreezePane(0,0);

        Sheet sheet2 = workBook.getSheetAt(1);
        sheet2.createFreezePane(0,0);
        //调用 方法exportParams为设置的sheet页信息 list为拼装好的导出字段信息  jsonObjects为数据项
//        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columns, jsonObject);
        FileOutputStream fileOutputStream = new FileOutputStream("D:\\home\\excel\\test1.xlsx");
        workBook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        workBook.close();
    }

    /**
     * 导出Ecel
     *
     * @return org.apache.poi.ss.usermodel.Workbook
     * @author zhuyongsheng
     * @date 2019/11/6
     */
    private static Workbook exportExcel(List<Map<String, Object>> list) {
        Workbook workbook = new HSSFWorkbook();
        for (Map<String, Object> map : list) {
            MyExcelExportService service = new MyExcelExportService();
            service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class,
                    (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get("data"));
        }
        return workbook;
    }


    //只导出表头
    @GetMapping("/exportExcel3")
    public void exportExcel3(HttpServletResponse response) throws IOException {
        List<ExcelExportEntity> columns = new ArrayList<>();
        for (int i =0;i<3;i++) {
            ExcelExportEntity idEntity = new ExcelExportEntity("caidan"+i, "filed"+i);
            columns.add(idEntity);
        }
        String excelName = "ce";
        ExportParams exportParams = new ExportParams(excelName, "sheet1");
        //调用 方法exportParams为设置的sheet页信息 list为拼装好的导出字段信息  jsonObjects为数据项
        List<JSONObject> jsonObject = new ArrayList<>();
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columns, jsonObject);
        Sheet sheet = workbook.getSheetAt(0);
        sheet.createFreezePane(0,0);
        FileOutputStream fileOutputStream = new FileOutputStream("D:\\home\\excel\\"+excelName+".xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        workbook.close();
    }


    /**
     * 获取导出流
     * @param response
     * @param fileName 导出文件名
     * @return
     */
    public ServletOutputStream getOutputStream(HttpServletResponse response, String fileName) throws IOException {

        fileName = URLEncoder.encode(fileName+".xlsx", "UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        //  客户端不缓存
        response.addHeader("Pragma", "no-cache");
        response.addHeader("Cache-Control", "no-cache");

        ServletOutputStream stream = response.getOutputStream();

        return stream;
    }

}
